package com.swufe.scoresforapp;

import com.swufe.scoresforapp.Yonghu.Dataimport;
import com.swufe.scoresforapp.bean.user;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Collection;

public class GradetoRank {
    static PreparedStatement ps1=null;
    static PreparedStatement ps2=null;
    static PreparedStatement ps3=null;
    static PreparedStatement ps4=null;

    static ResultSet rs1=null;
    static ResultSet rs2=null;
    static ResultSet rs3=null;

    public static void gradetorank(String uid){
        String rank ="";
        String rank_up ="";
        String rank_down ="";
        String str="";
        int ugrade_up;
        int ugrade_down;
        user u= com.swufe.scoresforapp.Collection.getUser(uid);
        String upl=u.getUplace();
        String ukelei=u.getUkelei();
        StringBuffer listname=new StringBuffer();

        int ugrade=u.getUgrade();  //获取用户信息表中的分数
        int uyear=u.getUyear();

        String year=Integer.toString(uyear);
        listname.append(year).append(upl).append(ukelei);  //构造一分一段表的表名

        ugrade_up=ugrade+15;  //假设比自己的分数高15分以内可以考虑冲一冲
        ugrade_down=ugrade-15;  //假设比自己的分数低15分以内的比较稳

        String grade=Integer.toString(ugrade);//因为一分一段表中分数为字符型
        String grade_up=Integer.toString(ugrade_up);//因为一分一段表中分数为字符型
        String grade_down=Integer.toString(ugrade_down);//因为一分一段表中分数为字符型

        try{
            Dataimport in = new Dataimport();
            ps1=in.getCon().prepareStatement("select 累计人数 from "+listname.toString()+" where 分数='"+grade+"'");
            rs1=ps1.executeQuery();
            ps2=in.getCon().prepareStatement("select 累计人数 from "+listname.toString()+" where 分数='"+grade_up+"'");
            rs2=ps2.executeQuery();
            ps3=in.getCon().prepareStatement("select 累计人数 from "+listname.toString()+" where 分数='"+grade_down+"'");
            rs3=ps3.executeQuery();
            while(rs1.next()){
                rank=rs1.getString(1);   //获得当年分数对应的排名
            }
            while(rs2.next()){
                rank_up=rs2.getString(1);   //获得分数上浮15分对应的排名
            }
            while(rs3.next()){           //获得分数下浮15分对应的排名
                rank_down=rs3.getString(1);
            }
            ps4=in.getCon().prepareStatement("update 用户信息表 set 排名="+Integer.parseInt(rank)+",上限排名="+Integer.parseInt(rank_up)+",分数上限="+ugrade_up+",分数下限="+ugrade_down+",下限排名="+Integer.parseInt(rank_down)+" where 用户账号='"+uid+"'");
            ps4.executeUpdate();

            in.getCon().close();
        }
        catch (Exception e) {
            System.out.println(e);
        }
    }
}
